package com.davinci.freemarket.dataAccessLayer.accessdata.categorias;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.davinci.freemarket.businessLayer.utils.DBInfoUtil;
import com.davinci.freemarket.dataAccessLayer.accessdata.DataBaseConnection;
import com.davinci.freemarket.dataModelLayer.model.CategoriasModel;
import com.davinci.freemarket.dataModelLayer.model.DBAttibuteModel;
import com.davinci.freemarket.dataModelLayer.model.PublicationModel;

/**
 * 
 * @author Lucas Buyo
 */
public class CategoriasDAO 
{
	/**
	 * 
	 * */
	protected DataBaseConnection dbcnx;
	
	/**
	 * 
	 * */
	protected Connection conn;
	
	
	//-------------------
	// CONSTRUCTOR
	//-------------------
	public CategoriasDAO() {
		this.dbcnx = new DataBaseConnection();
	}
	
	
	
	public ArrayList<CategoriasModel> selectAllCategorias(){
		
		ArrayList<CategoriasModel> categorias = new ArrayList<CategoriasModel>();
		conn = dbcnx.getConnection();
		String query = "SELECT * FROM CATEGORIAS";
		PreparedStatement preparedStament = null;
		try{
			preparedStament = conn.prepareStatement(query);
			
			ResultSet rs = preparedStament.executeQuery();
			
			while(rs.next()){
				CategoriasModel categoria = new CategoriasModel();
				categoria.setName(rs.getString("categoria"));
				categorias.add(categoria);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
			try {
				conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
				e1.printStackTrace();
			}
		}
		
		return categorias;
		
	}
	
	/**
	 * Selecciona categorias de la tabla CATEGORIAS segun una lista de condiciones
	 * 
	 * @param conditions
	 * @return
	 */
	public ResultSet selectCategorias(ArrayList<DBAttibuteModel> conditions){
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
		ResultSet rs = null;
		String sqlQuery = "";
		
		Boolean firstCondition = true;
		
		try {
			sqlQuery = "SELECT * " + "FROM ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.CATEGORIAS_TABLE+"] " +
					  	"WHERE";
			
			//-- CON LAS SIGUIENTES PROPIEDADES = {ATTRIBUTE, VALUE}
			for (DBAttibuteModel object : conditions) {
				sqlQuery += firstCondition ? " " + object.column + " LIKE ?" : " AND " + object.column + " LIKE ?"; 
				firstCondition = false;
			}
			
			statement = conn.prepareStatement(sqlQuery);
			
			int index = 1;
			for (DBAttibuteModel dbAttibuteModel : conditions) {
				statement.setString(index, dbAttibuteModel.value);
				index++;
			}
			
			rs = statement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
//		closeConnection();
		return rs;	
	}
	
	
	
	/**
	 * Agrega una nueva categoria a la tabla CATEGORIAS
	 * 
	 * @param categoria
	 * @return
	 */
	public int addCategoria(String categoria){
		conn = dbcnx.getConnection();
		PreparedStatement statement = null; 
        int rs = 0;
        
		try {
			statement = conn.prepareStatement("INSERT INTO ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.CATEGORIAS_TABLE+"] " +
											  	"VALUES (?);");
			statement.setString(1, categoria);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		closeConnection();
		return rs;
	} 
	
	
	/**
	 * Modifica un elemento de la tabla CATEGORIAS
	 * 
	 * @param id_cat
	 * @param categoria
	 * @return
	 */
	public int modifyCategoria(int id_cat, String categoria){
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
        int rs = 0;
        
		try {
			statement = conn.prepareStatement("UPDATE ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.CATEGORIAS_TABLE+"] " +
												"SET CATEGORIA = ? " +
											  	"WHERE ID_CAT = ?;");
			statement.setString(1, categoria);
			statement.setInt(2, id_cat);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		closeConnection();
		return rs;
	}
	
	
	/**
	 * Elimina una categoria de la tabla CATEGORIAS
	 */
	public int deleteCategoria(int id_cat){
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
		int rs = 0;
		
		try {
			statement = conn.prepareStatement("DELETE FROM ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.CATEGORIAS_TABLE+"] " +
												"WHERE ID_CAT = ?;");
			statement.setInt(1, id_cat);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		closeConnection();
		return rs;
		
	}
	
	
	
	/**
	 * 
	 * */
    public void closeConnection() {
        try {
            if (conn != null)
                conn.close();
            conn = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
	
}
